package dataLayer;

import model.User;
import interfaces.IDAOUser;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.TreeSet;


public class DAOUser extends DBconnect implements IDAOUser {

	private static DAOUser single;

	// Make SQL query and call to DB to adduser
	public void insertUser(User u) {
		try {

			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "call sp_add_user('" + u.getUser_Name() + "', '"
					+ u.getstringpassword() + "');";
			stmt.execute(sql);
			stmt.close();
			closeConn();

		} catch (Exception e) {
			System.err.println("SQL Error Dao insertUser");
		}
	}

	// Make SQL query and call to DB to loadUser
	@Override
	public String loadUser(User u) {
		User u1 = new User(null, null);
		String password = null;
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "call sp_load_user('" + u.getUser_Name() + "')";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {

				u1.setUser_Name(rs.getString("username"));
				u1.setStringpassword(rs.getString("password"));

				password = rs.getString("password");
			}

			stmt.close();
			closeConn();

		} catch (Exception e) {
			e.printStackTrace();
			System.err.println("SQL Error DAO Load User");
		}
		return password;

	}

	// Make SQL query and call to DB to Edituser
	public void updateUser(User u) {
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "call sp_update_user('" + u.getUser_Name() + "')";
			stmt.execute(sql);
			stmt.close();
			closeConn();
		} catch (Exception e) {
			System.err.println("SQL Error Dao Update User");

		}

	}

	// Make SQL query and call to DB to deleteuser
	public void deleteUser(User u) {
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "CALL `sp_delete_user`('" + u.getUser_Name() + "')";
			stmt.execute(sql);
			stmt.close();
			closeConn();

		} catch (Exception e) {
			System.err.println("SQL Error Dao Deleteuser");

		}
	}

	// Make SQL query and call to DB to loadAllusers
	public TreeSet<User> loadallUser() {

		TreeSet<User> userset = new TreeSet<User>();
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "call sp_load_all_users";
			ResultSet rs = stmt.executeQuery(sql);
			userset.clear();

			while (rs.next()) {
				User u = new User(null, null);
				u.setUser_Name(rs.getString("name"));

				userset.add(u);
			}

			stmt.close();
			closeConn();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println("SQL Error Dao LoadAllUser");
		}
		return userset;
	}

	public static DAOUser getInstance() {
		if (single == null) {
			single = new DAOUser();
		}
		return single;
	}

}
